/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package mainService;

import com.nct.framework.common.LogUtil;
import com.nct.framework.dbconn.ClientManager;
import com.nct.framework.dbconn.ManagerIF;
import com.nct.framework.util.ConvertUtils;
import config.ConfigInfo;
import extentEntity.ClassifiedMessageEnt;
import extentEntity.ClassifiedTopicEnt;
import extentEntity.TwitterPostEnt;
import extentEntity.TwitterPostListEnt;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import org.apache.log4j.Logger;

/**
 *
 * @author liempt
 */
public class DataBaseService {
    private static final Logger logger = LogUtil.getLogger(DataBaseService.class);
    
    public static long Create(TwitterPostEnt twitterPostEnt) {
        long resultValue = 0;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "INSERT INTO `Post` (`TweetId`, `Username`, `UserId`, `TweetContent`, `Message1`, `Message2`, `LinkUrl`, `Status`, `CreatedDate`) VALUE(?,?,?,?,?,?,?,?,?)";
            PreparedStatement stmt = cnn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.setLong(1, twitterPostEnt.TweetId);
            stmt.setString(2, twitterPostEnt.Username);
            stmt.setLong(3, twitterPostEnt.UserId);
            stmt.setString(4, twitterPostEnt.TweetContent);
            stmt.setString(5, twitterPostEnt.Message1);
            stmt.setString(6, twitterPostEnt.Message2);
            stmt.setString(7, twitterPostEnt.LinkUrl);
            stmt.setInt(8, twitterPostEnt.Status);
            stmt.setLong(9, twitterPostEnt.CreatedDate);
            
            
            int result = stmt.executeUpdate();
            if(result>0){
                ResultSet tableKeys = stmt.getGeneratedKeys();
                if(tableKeys.next()){
                    long autoGeneratedID = tableKeys.getLong(1);

                    resultValue = (autoGeneratedID>0) ? autoGeneratedID : 0;
                }
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return resultValue;
    } 
    
    public static long CreateUser(String username, long userTwitterId) {
        long resultValue = 0;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "SELECT * FROM `User` WHERE `Username` = ?";
            PreparedStatement stmt = cnn.prepareStatement(query);
            stmt.setString(1, username);
            ResultSet resultSet = stmt.executeQuery();
            if (resultSet.next()) {
                long tmpId = ConvertUtils.toLong(resultSet.getString("UserId"));
                if(tmpId>0){
                    return tmpId;
                }
            }
            stmt.close();
            
            String queryInsert = "INSERT INTO `User` (`UserIdTwitter`, `Username`) VALUE(?,?)";
            PreparedStatement stmtInsert = cnn.prepareStatement(queryInsert, Statement.RETURN_GENERATED_KEYS);
            stmtInsert.setLong(1, userTwitterId);
            stmtInsert.setString(2, username);
            int result = stmtInsert.executeUpdate();
            if(result>0){
                ResultSet tableKeys = stmtInsert.getGeneratedKeys();
                if(tableKeys.next()){
                    long autoGeneratedID = tableKeys.getLong(1);
                    resultValue = (autoGeneratedID>0) ? autoGeneratedID : 0;
                }
            }
            stmtInsert.close();
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return resultValue;
    }
    
    public static TwitterPostListEnt GetList(int postStatus) {
        TwitterPostListEnt listResult = new TwitterPostListEnt();
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "SELECT * FROM `Post` ";
            String queryCount = "SELECT Count(PostId) FROM `Post` ";
            String whereQuery = " WHERE 1=1 ";
            if(postStatus >= 0){
                whereQuery += "AND `Status`=" + postStatus + " ";
            }
            query = query + whereQuery + " ORDER BY `PostId` DESC;";
            queryCount = queryCount + whereQuery + " ;";
            PreparedStatement stmt = cnn.prepareStatement(query);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                TwitterPostEnt tmpTwitterPostEnt = new TwitterPostEnt();
                tmpTwitterPostEnt.PostId = ConvertUtils.toLong(resultSet.getString("PostId"));
                tmpTwitterPostEnt.TweetId = ConvertUtils.toLong(resultSet.getString("TweetId"));
                tmpTwitterPostEnt.Username = ConvertUtils.toString(resultSet.getString("Username"));
                tmpTwitterPostEnt.UserId = ConvertUtils.toLong(resultSet.getString("UserId"));
                tmpTwitterPostEnt.TweetContent = ConvertUtils.toString(resultSet.getString("TweetContent"));
                tmpTwitterPostEnt.Message1 = ConvertUtils.toString(resultSet.getString("Message1"));
                tmpTwitterPostEnt.Message2 = ConvertUtils.toString(resultSet.getString("Message2"));
                tmpTwitterPostEnt.LinkUrl = ConvertUtils.toString(resultSet.getString("LinkUrl"));
                tmpTwitterPostEnt.Status = ConvertUtils.toShort(resultSet.getString("Status"));
                
                listResult.listEnts.add(tmpTwitterPostEnt);
            }
            
            PreparedStatement stmtCount = cnn.prepareStatement(queryCount);
            ResultSet resultCount = stmtCount.executeQuery();
            while (resultCount.next()) {
                listResult.totalRecords = ConvertUtils.toLong(resultCount.getString(1));
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return listResult;
    }
    
    public static Vector<Vector<String>> GetDataClassifiedTopic() {
        Vector<Vector<String>> listResult = new Vector<Vector<String>>();
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "SELECT * FROM `ClassifiedTopic` ";
            PreparedStatement stmt = cnn.prepareStatement(query);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                Vector<String> vstring = new Vector<String>();
                vstring.add(resultSet.getString("TopicId"));
                vstring.add(resultSet.getString("TopicName"));
                vstring.add(resultSet.getString("Keyword"));
                vstring.add("\n\n\n\n\n\n\n");

                listResult.add(vstring);
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return listResult;
    }
    
    public static List<ClassifiedTopicEnt> GetListDataClassifiedTopic() {
        List<ClassifiedTopicEnt> listResult = new ArrayList<ClassifiedTopicEnt>();
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "SELECT * FROM `ClassifiedTopic` ";
            PreparedStatement stmt = cnn.prepareStatement(query);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                ClassifiedTopicEnt topicEnt = new ClassifiedTopicEnt();
                topicEnt.TopicId = ConvertUtils.toLong(resultSet.getString("TopicId"));
                topicEnt.TopicName = ConvertUtils.toString(resultSet.getString("TopicName"));
                topicEnt.Keyword = ConvertUtils.toString(resultSet.getString("Keyword"));
                listResult.add(topicEnt);
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return listResult;
    }
    
    public static Vector<Vector<String>> GetDataClassifiedMessage() {
        Vector<Vector<String>> listResult = new Vector<Vector<String>>();
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "SELECT * FROM `ClassifiedMessage` ";
            PreparedStatement stmt = cnn.prepareStatement(query);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                Vector<String> vstring = new Vector<String>();
                vstring.add(resultSet.getString("PostId"));
                vstring.add(resultSet.getString("TopicId"));
                vstring.add(resultSet.getString("Probability"));
                vstring.add("\n\n\n\n\n\n\n");

                listResult.add(vstring);
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return listResult;
    }
    
    public static Vector<Vector<String>> GetDataClassifiedMessage(long topicId, double probability) {
        Vector<Vector<String>> listResult = new Vector<Vector<String>>();
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
//            String query = "SELECT * FROM `ClassifiedMessage` WHERE `Probability` >= ? AND `TopicId` = ?;";
            String query = "SELECT `Post`.`UserId`, `Post`.`Username`, `Post`.`TweetContent`, `ClassifiedMessage`.`Probability`  FROM `ClassifiedMessage` LEFT JOIN `Post` ON(`Post`.`PostId`=`ClassifiedMessage`.`PostId`) WHERE `ClassifiedMessage`.`Probability` >= ? AND `ClassifiedMessage`.`TopicId`=?;";
            PreparedStatement stmt = cnn.prepareStatement(query);
            stmt.setDouble(1, probability);
            stmt.setLong(2, topicId);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {
                Vector<String> vstring = new Vector<String>();
                vstring.add(resultSet.getString("UserId"));
                vstring.add(resultSet.getString("Username"));
                vstring.add(resultSet.getString("Probability"));
                vstring.add("\n\n\n\n\n\n\n");

                listResult.add(vstring);
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return listResult;
    }
    
    
    
    public static boolean ClearDataClassified() {
        boolean result = false;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String queryClassifiedTopic = "TRUNCATE TABLE `ClassifiedTopic`;";
            String queryClassifiedMessage = "TRUNCATE TABLE `ClassifiedMessage`;";
            PreparedStatement stmt = cnn.prepareStatement(queryClassifiedTopic);
            result = stmt.executeUpdate() > 0;
            stmt.close();
            
            PreparedStatement stmtCount = cnn.prepareStatement(queryClassifiedMessage);
            result = stmtCount.executeUpdate() > 0;
            stmtCount.close();
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return result;
    }
    
    public static boolean Update(TwitterPostEnt twitterPostEnt) {
        boolean result = false;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            PreparedStatement stmt = cnn.prepareStatement("UPDATE `Post` SET `TweetId` = ?,`Username` = ?,`UserId`=?,`TweetContent`=?,`Message1`=?,`Message2`=?,`LinkUrl`=?,`Status`=? WHERE `PostId` = ?;");
            stmt.setLong(1, twitterPostEnt.TweetId);
            stmt.setString(2, twitterPostEnt.Username);
            stmt.setLong(3, twitterPostEnt.UserId);
            stmt.setString(4, twitterPostEnt.TweetContent);
            stmt.setString(5, twitterPostEnt.Message1);
            stmt.setString(6, twitterPostEnt.Message2);
            stmt.setString(7, twitterPostEnt.LinkUrl);
            stmt.setInt(8, twitterPostEnt.Status);
            stmt.setLong(9, twitterPostEnt.PostId);
            
            result = stmt.executeUpdate() > 0;
            stmt.close();
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
            return false;
        } finally {
            cm.returnClient(cnn);
        }
        return result;
    }
    
    public static long CreateClassifiedTopic(ClassifiedTopicEnt topicEnt) {
        long resultValue = 0;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "INSERT INTO `ClassifiedTopic` (`TopicName`, `Keyword`) VALUE(?,?)";
            PreparedStatement stmt = cnn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, topicEnt.TopicName);
            stmt.setString(2, topicEnt.Keyword);
            
            int result = stmt.executeUpdate();
            if(result>0){
                ResultSet tableKeys = stmt.getGeneratedKeys();
                if(tableKeys.next()){
                    long autoGeneratedID = tableKeys.getLong(1);

                    resultValue = (autoGeneratedID>0) ? autoGeneratedID : 0;
                }
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return resultValue;
    } 
    
    public static long CreateClassifiedMessage(ClassifiedMessageEnt messageEnt) {
        long resultValue = 0;
        ManagerIF cm = ClientManager.getInstance(ConfigInfo.SERVER_DB);
        Connection cnn = cm.borrowClient();
        try {
            String query = "INSERT INTO `ClassifiedMessage` (`PostId`, `TopicId`, `Probability`) VALUE(?,?,?)";
            PreparedStatement stmt = cnn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.setLong(1, messageEnt.PostId);
            stmt.setLong(2, messageEnt.TopicId);
            stmt.setDouble(3, messageEnt.Probability);
            
            int result = stmt.executeUpdate();
            if(result>0){
                ResultSet tableKeys = stmt.getGeneratedKeys();
                if(tableKeys.next()){
                    long autoGeneratedID = tableKeys.getLong(1);

                    resultValue = (autoGeneratedID>0) ? autoGeneratedID : 0;
                }
            }
        } catch (Exception ex) {
            logger.error(LogUtil.stackTrace(ex));
        } finally {
            cm.returnClient(cnn);
        }
        return resultValue;
    }
    
}
